package xin.yangda.xpoi.xls;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import com.alibaba.fastjson.JSONObject;

/**
 * 根据特定Excel导出Excel文档
 * 
 * @author izifeng
 * @version 1.0
 * @date 2017-12-08 11:03
 * @site https://gitee.com/izifeng/XPOI.git
 *
 */
public class WorkbookDesigner {

	private HSSFWorkbook workBook;

	// 数据源
	private List<JSONObject> dataSource = null;

	public ByteArrayOutputStream os = new ByteArrayOutputStream();

	// 存储自定义字段
	private HashMap<String, String> map = new HashMap<String, String>();

	/**
	 * 打开excel模板
	 * 
	 * @param filePath
	 *            excel模板路径
	 * @throws IOException
	 */
	public void open(String filePath) throws IOException {
		File file = new File(filePath);
		FileInputStream fis = new FileInputStream(file);

		POIFSFileSystem fs = new POIFSFileSystem(fis);

		// 读取excel模板
		workBook = new HSSFWorkbook(fs);
	}

	/**
	 * 添加数据源
	 * 
	 * @param list
	 */
	public void setDataSource(List<JSONObject> list) {
		dataSource = list;
	}

	/**
	 * 添加自定义字段
	 * 
	 * @param key
	 * @param value
	 */
	public void setDataSource(String key, String value) {
		map.put(key, value);
	}

	/**
	 * 数据处理
	 * 
	 * @throws IOException
	 */
	public void process() throws IOException {
		HSSFSheet sheet = workBook.getSheetAt(0);

		// 获得总列数
		int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
		// 获得总行数
		int rowNum = sheet.getLastRowNum();

		// 自定义字段键
		Set<String> setKey = map.keySet();

		// 数据源字段 单元格数据，存储 行、列、单元格样式
		JSONObject jsonField = new JSONObject();

		for (int rowIdx = 0; rowIdx <= rowNum; rowIdx++) {
			Row row = sheet.getRow(rowIdx);
			for (int colIdx = 0; colIdx < coloumNum; colIdx++) {
				Cell cell = row.getCell(colIdx);
				String cellValue = cell.getStringCellValue().trim();
				if (cellValue.length() > 0 && cellValue.indexOf("&=") == 0) {
					if (cellValue.indexOf("&=$") == 0) {
						// 填充自定义字段值
						for (String key : setKey) {
							StringBuffer sb = new StringBuffer();
							sb.append("&=$").append(key);
							if (cellValue.equals(sb.toString())) {
								cell.setCellValue(map.get(key));
							}
						}
					} else if (cellValue.indexOf("&=") == 0) {
						// 提取单元格信息
						String field = cellValue.replaceAll("&=", "");
						JSONObject jo = new JSONObject();
						jo.put("rowIdx", rowIdx);
						jo.put("colIdx", colIdx);
						jo.put("CellStyle", cell.getCellStyle());
						jsonField.put(field, jo);
					}
				}
			}
		}

		// 填充数据
		if (dataSource != null) {
			JSONObject firstRow = dataSource.get(0);
			Set<String> sKey = firstRow.keySet();
			Iterator<String> iterator = sKey.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				JSONObject jo = (JSONObject) jsonField.get(key);
				int rowIdx = jo.getInteger("rowIdx");
				int colIdx = jo.getInteger("colIdx");
				CellStyle cellStyle = (CellStyle) jo.get("CellStyle");

				for (int i = 0; i < dataSource.size(); i++) {
					Row row = sheet.getRow(rowIdx + i);
					if (row == null) {
						row = sheet.createRow(rowIdx + i);
					}

					Cell cell = row.getCell(colIdx);
					if (cell == null) {
						cell = row.createCell(colIdx);
					}

					cell.setCellStyle(cellStyle);

					JSONObject json = dataSource.get(i);
					cell.setCellValue(json.getString(key));
				}
			}
		}

		workBook.write(os);
		workBook.close();
	}

	/**
	 * 保存文件
	 * 
	 * @param filePath
	 *            目标文件路径
	 * @throws IOException
	 */
	public void saveFile(String filePath) throws IOException {
		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);

		FileOutputStream outputStream = new FileOutputStream(filePath);

		BufferedInputStream bis = new BufferedInputStream(is);
		BufferedOutputStream bos = new BufferedOutputStream(outputStream);

		byte[] buff = new byte[8192];
		int bytesRead;
		while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
			bos.write(buff, 0, bytesRead);
		}

		bis.close();
		bos.close();
		outputStream.flush();
		outputStream.close();
	}
}
